package custom;

import java.io.*;
import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;

public class support {

    public static Vector countries, universities, majors, specializations;
    public static int us_id = 213; // United State Country ID

    public static Vector getCountries() throws Exception{
        if(countries == null) { // if the countries vector is null, get countries from the database
            countries = new Vector();
            countries.add(""); // database IDs starts from index 1

            InitialContext cxt = new InitialContext();
            DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/postgres");
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from countries");
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
                countries.add(rs.getString("country"));
            }

            pstmt.close();
            conn.close();
        }
        return countries;
    }

    // The universities Vector:
    // [[country_state1, [[univ1, id1], [univ2, id2], ...]], ... ]
    public static Vector getUniversities() {
        if(universities == null) {
            universities = new Vector();
            try {
                InitialContext cxt = new InitialContext();

                DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/postgres");
                Connection conn = ds.getConnection();
                PreparedStatement pstmt = conn.prepareStatement("select * from universities order by country_state, university"); // first order by country_state, then by university
                ResultSet rs = pstmt.executeQuery();
                String lastCS = "";
                Vector tuple, univs = new Vector();
                while(rs.next()) {
                    String newCS = rs.getString("country_state");
                    if(!newCS.equals(lastCS)) {
                        tuple = new Vector();
                        tuple.add(newCS);
                        univs = new Vector();
                        tuple.add(univs);
                        universities.add(tuple);
                    }
                    lastCS = newCS;
                    Vector tmp = new Vector();
                    tmp.add(rs.getString("university"));
                    tmp.add(rs.getInt("id"));
                    univs.add(tmp);
                }

                pstmt.close();
                conn.close();
            }
            catch(Exception e) {
            }
        }
        return universities;
    }

    public static Vector getMajors() throws Exception {
        if(majors == null) {
            majors = new Vector();
            InitialContext cxt = new InitialContext();
            DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/postgres");
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from majors");
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
                Vector tmp = new Vector();
                tmp.add(rs.getInt("id"));
                tmp.add(rs.getString("major"));
                majors.add(tmp);
            }

            pstmt.close();
            conn.close();
        }
        return majors;
    }

    public static Vector getSpecializations() throws Exception {
        if(specializations == null) {
            specializations = new Vector();
            specializations.add(""); // database IDs starts from index 1

            InitialContext cxt = new InitialContext();
            DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/postgres");
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from specializations");
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
                specializations.add(rs.getString("specialization"));
            }

            pstmt.close();
            conn.close();
        }
        return specializations;
    }
}

